
import sys, traceback
import sqlite3 as sql
import math
import argparse

def get_median(sorted_data):
    n = len(sorted_data)
    # floor division
    if n % 2 == 1:  # If odd
        median = sorted_data[n // 2]
    else:  # If even
        mid1 = sorted_data[n // 2 - 1]
        mid2 = sorted_data[n // 2]
        median = (mid1 + mid2) // 2
    return median

def calculate(rows):
    n    = len(rows)
    if n < 2:
        return
    #    (9, '^xfff[^xf00NoLove^xeee]^xddd idle^7', 9722)
    times = [row[2] for row in rows]
    mean = sum(times) // n
    if n > 2:
        median = get_median(times)
    else:
        median = mean

    stdev = int()
    variance = int()
    for time in times:
        variance = (time - mean) ** 2
    variance = variance/n

    stdev = round(math.sqrt(variance))

    # print(times, file=sys.stderr)
    # print(mean, file=sys.stderr)
    # print(median, file=sys.stderr)
    # print(stdev, file=sys.stderr)
    return n, mean/100, median/100, (mean-median)/100, stdev/100

def main_loop(database, map_list):
    if map_list == []:
        return
    query = str();
    with open("queries/mleaderboard-ojoin.sql", 'r') as f:
        query = f.read()
    if query == str():
        return
    data = []
    # with open("test.html", 'w') as output:
    for game_map in map_list:
        result = []
        with sql.connect(database) as con:
            cursor = con.cursor()
            try:
                cursor.execute(query, game_map)
                result = cursor.fetchall()
            except sql.Error:
                traceback.print_exc()
                print("\n\t", game_map)
        try:
            # select Cts_ranks.mapid, max(trank), min(tvalue), alias
            if len(result) > 1:
                data.append ( [game_map[0], *calculate(result)] )
            # test output without spamming console
            # if game_map[0] == "zeel-omnitek":
            #     print(result, file=sys.stderr)
        except TypeError:
            traceback.print_exc()
            print("\n\t occurred given map ", game_map[0])
    return data

def to_html(data, footnotes=[str(),str()], ahref = str()):
    # print(data)
    table = list()
    header = """<table class='leaderboard'>
             <th colspan='6'> <h3><br/>Map List</H3> </th>
             <tr>
             <th>Name</th>
             <th>Records</th>
             <th>Mean</th>
             <th>Median</th>
             <th>Δ {explain1}</th>
            <th>σ {explain2}</th>
            </tr>"""
    table.append(
        header.format(
            explain1=footnotes[0], explain2=footnotes[1] 
        )
    )
    for d in data:
        if ahref != str():
            d[0] = "<a href=\"%s%s\">%s</a>" % (ahref, d[0],d[0])
        row = ["<td>%s</s>" % elem for elem in d]
        table.append("\t<tr>%s</tr>\n" % "".join(row))
    table.append("</table>")
    return table

def generate_page(html_template, data):
    template = str()
    with open(html_template,'r') as f:
        template = f.read()
    with open("test.html", 'w') as output:
        output.write(template % "".join(data))
    return len(data)

# get maps in same order asother pages
def get_map_list(db):
    map_list = []
    query  = str()
    result = []
    with open("queries/mranks.sql", 'r') as f:
        query = f.read()
    with sql.connect(db) as con:
        try:
            cursor = con.cursor()
            cursor.execute(query)
            result = cursor.fetchall()
        except sql.Error:
            pass
    return [ (row[0],) for row in  result] 

def main(p1, p2, p3, p4):
    database = p1 or "db/cts.db"
    template = p2 or "templates/overview.html"
    url = p3 or "/xdf/maps/"
    notes = p4

    maps = get_map_list(database)
    line_count = generate_page(
        template,
        to_html(main_loop(database, maps), footnotes=notes, ahref=url)
    )
    print("wrote %i lines to template %s" \
        % (line_count, template))
    return

if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument(
        '-d','--db',
        help="database that is queried by the script"
    )
    parser.add_argument(
        '-t', '--tpl',
        help="the html template to generate the page from"
    )
    parser.add_argument(
        '-l', '--link',
        help="folder or link, creates anchor tags on map names"
    )
    parser.add_argument(
        '-f1', '--footnote1',
        help="insert html (a footnote or tooltip) into <th> of delta"
    )
    parser.add_argument(
        '-f2', '--footnote2',        
        help="insert html (a footnote or tooltip) into <th> of sigma"
    )
#     parser.add_argument(
#         
#     )
    a  = parser.parse_args()
    f1 = a.footnote1 or str()
    f2 = a.footnote2 or str()
    args = [a.db, a.tpl, a.link, [f1,f2] ]
    main(*args)
